<?php

namespace App\Models\School;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Student extends Model
{
    protected $table = 'jxt_school_student';

    protected $primaryKey = 'student_id';

    public function classes()
    {
        return $this->belongsTo(Classes::class,'class_id');
    }


    public function getStudentList($class_id)
    {
        /*$a = $this->table;
        return $this->select([$a.'.student_id',$a.'.name_class',$a.'.icon_class','SUM(b.score) as score','e.telephone','e.username'])
            ->leftJoin('jxt_school_student_score as b',function ($join) use ($a,$class_id){
                $join->on('b.student_id',$a.'.student_id')
                    ->on('b.class_id',$class_id);
            })
            ->leftJoin('jxt_school_child_student as c','c.student_id',$a.'.student_id')
            ->leftJoin('jxt_sys_child as d','b.child_id','c.child_id')
            ->leftJoin('jxt_xp_user as e','e.id','d.create_by')
            ->where([
                ['b.class_id',$class_id],
                ['c.status',1],
                ['d.status',1],
                ['e.status',1],
                [$a.'.status',1],
                [$a.'.class_id',$class_id],
            ])
            ->groupBy('b.student_id');*/
    }

    public function getList($class_id)
    {
        $a = $this->table;



        return $this->select([$a.'.student_id',$a.'.name_class',$a.'.icon_class','e.telephone','e.username',DB::raw('sum(b.score)')])
            ->leftJoin('jxt_school_student_score as b',function ($join) use ($a,$class_id){
                $join->on('b.student_id',$a.'.student_id');
            })
            ->leftJoin('jxt_school_child_student as c',function ($join) use ($a){
                $join->on('c.student_id',$a.'.student_id');
            })
            ->leftJoin('jxt_sys_child as d',function ($join) use ($a){
                $join->on('d.child_id','c.child_id');
            })
            ->leftJoin('jxt_xp_user as e',function ($join) use ($a){
                $join->on('e.id','d.create_by');
            })
            ->where([
                ['b.class_id',$class_id],
                [$a.'.status',1],
                [$a.'.class_id',$class_id],
            ])
            ->groupBy('b.student_id');

        return $this->select([$a.'.student_id',$a.'.name_class',$a.'.icon_class','e.telephone','e.username'])
            ->leftJoin('jxt_school_student_score as b',function ($join) use ($a,$class_id){
                $join->on('b.student_id',$a.'.student_id')
                    ->on('b.class_id',$class_id);
            })
            ->leftJoin('jxt_school_child_student as c','c.student_id',$a.'.student_id')
            ->leftJoin('jxt_sys_child as d','d.child_id','c.child_id')
            ->leftJoin('jxt_xp_user as e','e.id','d.create_by')
            ->where([
                ['b.class_id',$class_id],
                ['c.status',1],
                ['d.status',1],
                ['e.status',1],
                [$a.'.status',1],
                [$a.'.class_id',$class_id],
            ])
            ->groupBy('b.student_id')
            ->sum('b.score');
    }

}
